// util/DatabaseUtil.java
package main.java.com.cinema.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 数据库连接工具类
 */
public class DatabaseUtil {

    // 数据库驱动程序
    static {
        try {
            // 加载SQLite驱动
            Class.forName("org.sqlite.JDBC");

            // 可以根据需要加载其他数据库驱动
            // Class.forName("com.mysql.cj.jdbc.Driver");
            // Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            // Class.forName("org.postgresql.Driver");
        } catch (ClassNotFoundException e) {
            System.err.println("找不到数据库驱动程序: " + e.getMessage());
            e.printStackTrace();
        }
    }

    /**
     * 获取数据库连接
     * @return 数据库连接对象
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        String url = "jdbc:sqlite:cinema.db";

        try {
            return DriverManager.getConnection(url);
        } catch (SQLException e) {
            System.err.println("数据库连接失败，URL: " + url);
            throw e;
        }
    }

    /**
     * 初始化数据库表结构
     */
    public static void initializeDatabase() {
        try (Connection conn = getConnection()) {
            createUserTable(conn);
            createMovieTable(conn);
            createHallTable(conn);
            createShowtimeTable(conn);
            createTicketTable(conn);
            System.out.println("数据库表初始化完成");
        } catch (SQLException e) {
            System.err.println("数据库初始化失败: " + e.getMessage());
            e.printStackTrace();
        }
    }

    /**
     * 创建用户表
     * @param conn 数据库连接
     * @throws SQLException
     */
    private static void createUserTable(Connection conn) throws SQLException {
        String sql = "CREATE TABLE IF NOT EXISTS users (" +
                "user_id VARCHAR(50) PRIMARY KEY, " +
                "username VARCHAR(50) NOT NULL UNIQUE, " +
                "password VARCHAR(255) NOT NULL, " +
                "role VARCHAR(20) NOT NULL, " +
                "phone_number VARCHAR(20), " +
                "registration_time DATETIME, " +
                "password_changed BOOLEAN DEFAULT FALSE" +
                ")";
        try (Statement stmt = conn.createStatement()) {
            stmt.execute(sql);
        }
    }

    /**
     * 创建电影表
     * @param conn 数据库连接
     * @throws SQLException
     */
    private static void createMovieTable(Connection conn) throws SQLException {
        String sql = "CREATE TABLE IF NOT EXISTS movies (" +
                "id VARCHAR(50) PRIMARY KEY, " +
                "title VARCHAR(100) NOT NULL, " +
                "director VARCHAR(50), " +
                "actors TEXT, " +
                "duration INTEGER, " +
                "description TEXT, " +
                "release_date DATE, " +
                "genre VARCHAR(50), " +
                "rating VARCHAR(10)" +
                ")";
        try (Statement stmt = conn.createStatement()) {
            stmt.execute(sql);
        }
    }

    /**
     * 创建放映厅表
     * @param conn 数据库连接
     * @throws SQLException
     */
    private static void createHallTable(Connection conn) throws SQLException {
        String sql = "CREATE TABLE IF NOT EXISTS halls (" +
                "id VARCHAR(50) PRIMARY KEY, " +
                "name VARCHAR(50) NOT NULL, " +
                "rows INTEGER, " +
                "columns INTEGER" +
                ")";
        try (Statement stmt = conn.createStatement()) {
            stmt.execute(sql);
        }
    }

    /**
     * 创建场次表
     * @param conn 数据库连接
     * @throws SQLException
     */
    private static void createShowtimeTable(Connection conn) throws SQLException {
        String sql = "CREATE TABLE IF NOT EXISTS showtimes (" +
                "id VARCHAR(50) PRIMARY KEY, " +
                "movie_id VARCHAR(50) NOT NULL, " +
                "hall_id VARCHAR(50) NOT NULL, " +
                "start_time DATETIME NOT NULL, " +
                "end_time DATETIME NOT NULL, " +
                "price DECIMAL(10, 2) NOT NULL" +
                ")";
        try (Statement stmt = conn.createStatement()) {
            stmt.execute(sql);
        }
    }

    /**
     * 创建票务表
     * @param conn 数据库连接
     * @throws SQLException
     */
    private static void createTicketTable(Connection conn) throws SQLException {
        String sql = "CREATE TABLE IF NOT EXISTS tickets (" +
                "id VARCHAR(50) PRIMARY KEY, " +
                "showtime_id VARCHAR(50) NOT NULL, " +
                "user_id VARCHAR(50) NOT NULL, " +
                "seat_row INTEGER NOT NULL, " +
                "seat_column INTEGER NOT NULL, " +
                "purchase_time DATETIME NOT NULL, " +
                "price DECIMAL(10, 2) NOT NULL, " +
                "status VARCHAR(20) NOT NULL" +
                ")";
        try (Statement stmt = conn.createStatement()) {
            stmt.execute(sql);
        }
    }
}
